package org.lisy.java.io;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.CreationHelper;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.RichTextString;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

/**
 * excel解析/生成(数据抽取案例)
 * 
 * @author lisy
 */
public class ExcelOperate {

	// 原始文件目录
	private final static String RIGINAL_FILE_DIR = "./deploy/riginal";
	// 原始文件
	private final static String RIGINAL_FILE = "riginal.xlsx";
	// 目标文件目录
	private final static String TARGET_FILE_DIR = "./deploy/target/excel";
	// 目标文件
	private final static String TARGET_FILE = "target.xlsx";
	// 目标文件
	private final static String SXSSF_TARGET_FILE = "sxssf_target.xlsx";

	// 提取sheet名
	private final static String SHEET_NAME = "test_name";
	// 提取开始列
	private final static int START_LINE = 2;
	// 提取结束列
	private final static int END_LINE = 4;
	// 开始标记
	private final static String START_FLAG = "START";
	// 结束标记
	private final static String END_FLAG = "END";

	public static void main(String[] args) {
		// 解析excel，数据抽取
		List<List<String>> list = analysisExcel();
		System.out.println("data extraction completed: " + list);
		if (list != null && !list.isEmpty()) {
			// 使用 HSSFWorkbook 或 XSSFWorkbook 生成文件
			loadData(list);
			// 使用 SXSSFWorkbook 生成文件
			genFile(list);
		}
	}

	private static List<List<String>> analysisExcel() {
		Workbook workbook = null;
		try {
			List<List<String>> list = new ArrayList<List<String>>();
			String filePath =  RIGINAL_FILE_DIR + File.separator + RIGINAL_FILE;
			workbook = WorkbookFactory.create(new FileInputStream(new File(filePath)));
			// 获取sheet页
			Sheet sheet = workbook.getSheet(SHEET_NAME);
			if (sheet == null) {
				System.out.println("sheet not available: " + SHEET_NAME);
				return null;
			}
			// 解决公式问题
			FormulaEvaluator evaluator = workbook.getCreationHelper().createFormulaEvaluator();
			boolean add = false;
			for (int rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) {
				Row row = sheet.getRow(rowNum);
				if (row != null) {
					List<String> subList = new ArrayList<String>();
					String rowStr = "";
					for (int j = START_LINE; j <= END_LINE; j++) {
						if (row.getCell(j) != null && !"".equals(row.getCell(j).toString())) {
							Cell cell = row.getCell(j);
							String value = row.getCell(j).toString();
							if (CellType.FORMULA.equals(cell.getCellType())) {
								CellValue cellValue = evaluator.evaluate(row.getCell(j));
								switch (cellValue.getCellType()) {
								    case BOOLEAN:
								        value = String.valueOf(cellValue.getBooleanValue());
								        break;
								    case NUMERIC:
								    	value = String.valueOf(cellValue.getNumberValue());
								        break;
								    case STRING:
								    	value = String.valueOf(cellValue.getStringValue());
								        break;
								    default:
								    	break;
								}
							}
							if (rowStr.length() > 0) {
								rowStr += ",";
							}
							rowStr += value;
							subList.add(value);
						} else {
							subList.add(null);
						}
					}
					// 判断是否结束
					if (add && rowStr.equals(END_FLAG)) {
						break;
					}
					if (add) {
						list.add(subList);
					}
					// 判断是否为标题，若是开始记录
					if (!add && rowStr.equals(START_FLAG)) {
						add = true;
					}
					
				}
			}
			workbook.close();
			return list;
		} catch (Exception e) {
			System.out.println("Excel data extraction error！" + e);
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return null;
	}

	private static boolean loadData(List<List<String>> list) {
		Workbook workbook = null;
		OutputStream out = null;
		String filePath =  TARGET_FILE_DIR + File.separator + TARGET_FILE;
		File file = new File(filePath);
		if (!file.exists()) {
			// 如果上级不存在，就会自动创建
			file.getParentFile().mkdirs();
		}
		int index = TARGET_FILE.lastIndexOf(".");
		String suffix = TARGET_FILE.substring(index);
		String tempFilePath = TARGET_FILE_DIR +  File.separator + TARGET_FILE.substring(0, index) + "_temp" + suffix;
		try {
			// 判断文件格式
			if (".xls".equalsIgnoreCase(suffix)) {
				workbook = WorkbookFactory.create(false);
			} else if (".xlsx".equalsIgnoreCase(suffix)) {
				workbook = WorkbookFactory.create(true);
			} else {
				System.out.println("This format file is not currently supported: " + suffix);
				return false;
			}
			System.out.println("Prepare to generate temporary files: " + tempFilePath);
			File tempFile = new File(tempFilePath);
			if (tempFile.exists()) {
				boolean delFiles = delFiles(tempFilePath);
				if (!delFiles) {
					System.out.println("Failed to delete temporary files!");
					return false;
				}
			}
			CreationHelper creationHelper = workbook.getCreationHelper();
			Sheet sheet = workbook.createSheet(SHEET_NAME);
			// 单元格格式
			DataFormat dataFormat = workbook.createDataFormat();
			// 单元格样式
			CellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setWrapText(true); // 设置自动换行
			cellStyle.setDataFormat(dataFormat.getFormat("@")); // 文本
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
			cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); // 设置背景颜色
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充背景颜色
			// 字体样式
			Font font = workbook.createFont();
			font.setBold(true); // 加粗
			font.setColor(IndexedColors.ORANGE.getIndex()); // 字体颜色
			for (int i = 0; i < list.size(); i++) {
				Row row = sheet.createRow(i);
				for (int j = 0; j < list.get(i).size(); j++) {
					Cell cell = row.createCell(j);
					String value = list.get(i).get(j);
					if (i == 0) {
						cell.setCellStyle(cellStyle);
						if (value != null) {
							// 富文本操作
							RichTextString richTextString = creationHelper.createRichTextString(value);
							richTextString.applyFont(0, value.length(), font);
							cell.setCellValue(richTextString);
						} else {
							cell.setCellValue(value);
						}
					} else {
						cell.setCellValue(value);
					}
					// 宽度控制，单元格有宽度限制
        			int cCount = calculateChineseNumber(value);
            		int columnWidth = (value.length() - cCount) * 256 + (cCount + 1) * 512;
            		if(columnWidth > sheet.getColumnWidth(j)) {
            			if (columnWidth > 255*256) {
                			columnWidth = 255*256;
                		}
            			sheet.setColumnWidth(j, columnWidth);
            		}
				}
			}
            out = new FileOutputStream(tempFilePath);
            workbook.write(out);
            workbook.close();
            out.close();
            System.out.println("The generation of the temporary file is completed, ready to be renamed to the official file: " + filePath);
            return renameFile(tempFile, filePath);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Excel data generation error! " + e);
			return false;
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	private static boolean genFile(List<List<String>> list) {
		Workbook workbook = null;
		OutputStream out = null;
		String filePath =  TARGET_FILE_DIR + File.separator + SXSSF_TARGET_FILE;
		File file = new File(filePath);
		if (!file.exists()) {
			// 如果上级不存在，就会自动创建
			file.getParentFile().mkdirs();
		}
		int index = SXSSF_TARGET_FILE.lastIndexOf(".");
		String suffix = SXSSF_TARGET_FILE.substring(index);
		if (".xls".equalsIgnoreCase(suffix)) {
			return false;
		}
		String tempFilePath = TARGET_FILE_DIR +  File.separator + SXSSF_TARGET_FILE.substring(0, index) + "_temp" + suffix;
		try {
			System.out.println("Prepare to generate temporary files: " + tempFilePath);
			File tempFile = new File(tempFilePath);
			if (tempFile.exists()) {
				boolean delFiles = delFiles(tempFilePath);
				if (!delFiles) {
					System.out.println("Failed to delete temporary files!");
					return false;
				}
			}
			// 每两条数据写入一次磁盘
			workbook = new SXSSFWorkbook(2);
			Sheet sheet = workbook.createSheet(SHEET_NAME);
			// 单元格格式
			DataFormat dataFormat = workbook.createDataFormat();
			// 单元格样式
			CellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setWrapText(true); // 设置自动换行
			cellStyle.setDataFormat(dataFormat.getFormat("@")); // 文本
			cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
			cellStyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.getIndex()); // 设置背景颜色
			cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND); // 填充背景颜色
			// SXSSF 使用富文本不生效，采用单元格设置字体样式
			Font font = workbook.createFont();
			font.setBold(true); // 加粗
			font.setColor(IndexedColors.ORANGE.getIndex()); // 字体颜色
			cellStyle.setFont(font);
			for (int i = 0; i < list.size(); i++) {
				Row row = sheet.createRow(i);
				for (int j = 0; j < list.get(i).size(); j++) {
					Cell cell = row.createCell(j);
					String value = list.get(i).get(j);
					if (i == 0) {
						cell.setCellStyle(cellStyle);
						cell.setCellValue(value);
					} else {
						cell.setCellValue(value);
					}
					// 宽度控制，单元格有宽度限制
        			int cCount = calculateChineseNumber(value);
            		int columnWidth = (value.length() - cCount) * 256 + (cCount + 1) * 512;
            		if(columnWidth > sheet.getColumnWidth(j)) {
            			if (columnWidth > 255*256) {
                			columnWidth = 255*256;
                		}
            			sheet.setColumnWidth(j, columnWidth);
            		}
				}
			}
			out = new FileOutputStream(tempFilePath);
            workbook.write(out);
            workbook.close();
            out.close();
            System.out.println("The generation of the temporary file is completed, ready to be renamed to the official file: " + filePath);
            return renameFile(tempFile, filePath);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("Excel data SXSSF generation error! " + e);
			return false;
		} finally {
			if (workbook != null) {
				try {
					workbook.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
			if (out != null) {
				try {
					out.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}

	/**
	 * 校验中文字符数量
	 * 
	 * @param str
	 * @return
	 */
	public static int calculateChineseNumber(String str) {
		int count = 0;
		String regEx ="[\\u4e00-\\u9fa5]";
		Pattern pattern = Pattern.compile(regEx);
		Matcher matcher = pattern.matcher(str);
		while (matcher.find()) {
			for (int i = 0; i <= matcher.groupCount(); i++) {
				count++;
			}
		}
		return count;
	}

    private static boolean renameFile(File targetFile, String fileName) {
    	boolean result = false;
		// 删除原文件
		if(deleteFile(fileName)) {
			File file = new File(fileName);
			// 重命名文件
			result = targetFile.renameTo(file);
			if (!result) {
				System.out.println("File renaming failed!");
			}
		} else {
			System.out.println("File renaming failed, delete File failed!");
		}
		return result;
	}

    private static boolean deleteFile(String fileName) {
        File file = new File(fileName);
        // 如果文件路径所对应的文件存在，并且是一个文件，则直接删除
        if (file.exists() && file.isFile()) {
            if (!file.delete()) {
                System.out.println("Delete Files " + fileName + " failed!");
                return false;
            }
        }
        return true;
    }

    /**
     * 删除目录及目录下的所有子目录和文件
     * 
     * @param path 文件或目录路径
     * @return 
     */
    public static boolean delFiles(String path) {
        boolean result = false;
        File file = new File(path);
        // 判断目录
        if (file.isDirectory()) {
            File[] childrenFiles = file.listFiles();
            for (File childFile : childrenFiles) {
                result = delFiles(childFile.getPath());
                if (!result) {
                    return result;
                }
            }
        }
        // 删除 文件、空目录
        return file.delete();
    }

}
